Oracle Database icon

Oracle Database

Upsert, get, add and update data in Oracle database

Overview

This node allows you to execute custom SQL queries against an Oracle Database from within n8n workflows. It is useful for retrieving, updating, inserting, or deleting data in your Oracle database directly from your automation flows. Common scenarios include:

  • Fetching records for reporting or further processing.
  • Updating or inserting data as part of a business process.
  • Running administrative or maintenance queries.

Example use cases:

  • Retrieve all customers with overdue invoices and send reminders.
  • Insert new order data received from another system.
  • Update product prices in bulk based on external input.

Properties

Name Meaning
SQL Statement The SQL query to execute. You can enter any valid Oracle SQL statement (e.g., SELECT, INSERT, UPDATE, DELETE). Example placeholder: SELECT id, name FROM product WHERE id < 40

Output

The output is a JSON array where each element represents a row returned by the SQL query. Each object contains key-value pairs corresponding to the column names and their values from the result set.

Example output:

[
  {
    "id": 1,
    "name": "Product A"
  },
  {
    "id": 2,
    "name": "Product B"
  }
]
  • If the query does not return rows (e.g., for INSERT/UPDATE), the output may be an empty array or contain metadata about the operation.

Dependencies

  • Oracle Database: Requires access to an Oracle Database instance.
  • Credentials: Needs oracleCredentials configured in n8n, including:
    • Username
    • Password
    • Connection String
    • (Optional) Thin mode flag
  • External Library: Uses the oracledb Node.js package.

Troubleshooting

  • Common Issues:

    • Invalid Credentials: If the username, password, or connection string are incorrect, the node will fail to connect.
    • SQL Syntax Errors: Malformed SQL statements will cause execution errors.
    • Network Issues: Firewalls or network restrictions may block access to the Oracle server.
    • Resource Limits: Large result sets may cause memory issues.

  • Error Messages:

    • "ORA-XXXXX": Oracle-specific error codes indicating issues like invalid SQL, missing tables, or permission problems.
    • "Failed to close the database connection": Indicates that the node could not properly close the connection; usually non-critical but should be monitored.

How to resolve:

  • Double-check credentials and connection details.
  • Test your SQL statement in a database client before using it in n8n.
  • Ensure the Oracle server is reachable from the n8n host.
  • Review Oracle error codes for specific troubleshooting steps.

Links and References

Discussion